-- Upate EFormNo Character Length	Varchar(200)
-- Cascade Window Wait For E-Form Update...


BEGIN TRANSACTION
GO
ALTER TABLE dbo.Voucher ADD
	ScaleM numeric(13, 2) NOT NULL CONSTRAINT DF_Voucher_ScaleM DEFAULT 0
GO
COMMIT

----------------------------------------------------------------

BEGIN TRANSACTION
GO

UPDATE Voucher SET 
	ScaleM = (SELECT SUM(Scale) AS Scale FROM VoucherDetails WHERE VoucherDetails.VoucherID = Voucher.VoucherID )
WHERE VoucherNo Like 'SI-%'

GO
COMMIT

----------------------------------------------------------------

USE [BSE]
GO
/****** Object:  View [dbo].[VW_Vouchers2]    Script Date: 12/27/2019 00:04:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*---------------------------------------------------------------------------------------------------------------------*/
ALTER VIEW [dbo].[VW_Vouchers2]
AS
SELECT     Voucher_1.VoucherID, Voucher_1.VoucherDate, Voucher_1.VoucherNo, Voucher_1.SessionID, dbo.SessionInfo.SessionTitle, dbo.SessionInfo.SessionFrom, dbo.SessionInfo.SessionTo, 
                      dbo.SessionInfo.CurrentSession, Voucher_1.Remarks AS MasterRemarks, Voucher_1.ReadOnly, Voucher_1.BankAccountID, Voucher_1.LoginID, dbo.Employees.EmployeeName, 
                      Voucher_1.HostName, Voucher_1.EntryDateTime, Accounts_2.AccountTitle AS BankAccountTitle, Voucher_1.ModifyID, Employees_1.EmployeeName AS ModifyName, Voucher_1.ModifyHostName, 
                      Voucher_1.ModifyDateTime, ISNULL(Voucher_1.EFormNo, dbo.Voucher.EFormNo) AS EFormNo, Voucher_1.ReturnDate, Voucher_1.AgentID, Accounts_1.AccountTitle AS AgentName, 
                      Voucher_1.PartyID, Accounts_3.AccountTitle AS PartyName, Accounts_3.CellNo AS PartyCellNo, Accounts_3.Phone AS PartyPhoneNo, Accounts_3.Remarks AS PartyAddress, Voucher_1.BLDate, 
                      Voucher_1.BLNo, Voucher_1.ContainerTypeID, Groups_2.GroupName AS ContainerType, Voucher_1.NoOfContainers, Voucher_1.ShipID, Groups_1.GroupName AS ShipName, Voucher_1.SupplierID, 
                      Accounts_1.AccountTitle AS SupplierName, Accounts_1.CellNo AS SupplierCellNo, Accounts_1.Phone AS SupplierPhoneNo, Accounts_1.Remarks AS SupplierAddress, Voucher_1.TransporterID, 
                      Accounts_4.AccountTitle AS TransporterName, Accounts_4.CellNo AS TransporterCellNo, Accounts_4.Phone AS TransporterPhoneNo, Accounts_4.Remarks AS TransporterAddress, Voucher_1.VoyNo, 
                      dbo.VoucherDetails2.EFormID, dbo.VoucherDetails2.ContainerNo, dbo.VoucherDetails2.Type, dbo.VoucherDetails2.ItemID, dbo.VoucherDetails2.Scale, dbo.VoucherDetails2.NetWeight, 
                      dbo.VoucherDetails2.GrossWeight, dbo.VoucherDetails2.NetWeightTotal, dbo.VoucherDetails2.GrossWeightTotal, Voucher_1.ContainerNo AS ContainerNoMaster, Voucher_1.MiscAccountID, 
                      Voucher_1.NetInvoice, Accounts_3.AreaID, dbo.Area.AreaName, Voucher_1.ShipLineID, Groups_3.GroupName AS ShipLine, Voucher_1.Currency, Voucher_1.SubPartyID, 
                      ISNULL(Groups_4.GroupName, '.') AS SubPartyName, Voucher_1.GD, Voucher_1.LoadQTY, Voucher_1.SalesQTY, Voucher_1.StatusID, dbo.Groups.GroupName AS StatusName, 
                      dbo.VoucherDetails2.LoadQTYD, dbo.VoucherDetails2.LoadQTYD2, dbo.VoucherDetails2.SalesQTYD, Voucher_1.ScaleM
FROM         dbo.VoucherDetails2 LEFT OUTER JOIN
                      dbo.Voucher ON dbo.VoucherDetails2.EFormID = dbo.Voucher.VoucherID RIGHT OUTER JOIN
                      dbo.Accounts RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_4 RIGHT OUTER JOIN
                      dbo.Groups AS Groups_4 RIGHT OUTER JOIN
                      dbo.Voucher AS Voucher_1 LEFT OUTER JOIN
                      dbo.Groups ON Voucher_1.StatusID = dbo.Groups.GroupID ON Groups_4.GroupID = Voucher_1.SubPartyID LEFT OUTER JOIN
                      dbo.Groups AS Groups_3 ON Voucher_1.ShipLineID = Groups_3.GroupID ON Accounts_4.AccountNo = Voucher_1.TransporterID ON dbo.Accounts.AccountNo = Voucher_1.SupplierID ON 
                      dbo.VoucherDetails2.VoucherID = Voucher_1.VoucherID LEFT OUTER JOIN
                      dbo.Groups AS Groups_1 ON Voucher_1.ShipID = Groups_1.GroupID LEFT OUTER JOIN
                      dbo.Groups AS Groups_2 ON Voucher_1.ContainerTypeID = Groups_2.GroupID LEFT OUTER JOIN
                      dbo.Area RIGHT OUTER JOIN
                      dbo.Accounts AS Accounts_3 ON dbo.Area.AreaID = Accounts_3.AreaID ON Voucher_1.PartyID = Accounts_3.AccountNo LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON Voucher_1.AgentID = Accounts_1.AccountNo LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_2 ON Voucher_1.BankAccountID = Accounts_2.AccountNo LEFT OUTER JOIN
                      dbo.Employees AS Employees_1 ON Voucher_1.ModifyID = Employees_1.EmployeeID LEFT OUTER JOIN
                      dbo.Employees ON Voucher_1.LoginID = dbo.Employees.EmployeeID LEFT OUTER JOIN
                      dbo.SessionInfo ON Voucher_1.SessionID = dbo.SessionInfo.SessionID